﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using Common.Utilities;
using Common.Utilities.Dispatcher;

namespace Workflows.Components.ETL
{
	/// <summary>
	/// parse excel file into data set containing tables each for one worksheet (excluding chart sheet)
	/// </summary>
	[Serializable]
	public class ExcelReaderInstruction:InstructionBase 
	{
		#region props
		private string _ExcelFilePath;
		/// <summary></summary>
		public string ExcelFilePath
		{
			get
			{
				return this._ExcelFilePath;
			}
			set
			{
				this._ExcelFilePath = value;
			}
		}

        private Dictionary<string,Dictionary<string,SimpleDataType>> _ColumnDataTypes;
        /// <summary></summary>
        public Dictionary<string,Dictionary<string,SimpleDataType>> ColumnDataTypes
        {
            get
            {
                return this._ColumnDataTypes;
            }
            set
            {
                this._ColumnDataTypes = value;
            }
        }

        private Dictionary<string,Dictionary<string,int>> _ColumnSizes;
        /// <summary></summary>
        public Dictionary<string,Dictionary<string,int>> ColumnSizes
        {
            get
            {
                return this._ColumnSizes;
            }
            set
            {
                this._ColumnSizes = value;
            }
        }

		private string _OutputFilePath;
		/// <summary>xml file path</summary>
		public string OutputFilePath
		{
			get
			{
				return this._OutputFilePath;
			}
			set
			{
				this._OutputFilePath = value;
			}
		}

        private string _TgtConnStr;
        /// <summary></summary>
        public string TgtConnStr
        {
            get
            {
                return this._TgtConnStr;
            }
            set
            {
                this._TgtConnStr = value;
            }
        }

        private string _TgtSchemaName;
        /// <summary></summary>
        public string TgtSchemaName
        {
            get
            {
                return this._TgtSchemaName;
            }
            set
            {
                this._TgtSchemaName = value;
            }
        }

        private string _TgtTableName;
        /// <summary></summary>
        public string TgtTableName
        {
            get
            {
                return this._TgtTableName;
            }
            set
            {
                this._TgtTableName = value;
            }
        }

        private bool _ClearExistingData;
        /// <summary></summary>
        public bool ClearExistingData
        {
            get
            {
                return this._ClearExistingData;
            }
            set
            {
                this._ClearExistingData = value;
            }
        }

        private bool _RecreateTgtTable;
        /// <summary></summary>
        public bool RecreateTgtTable
        {
            get
            {
                return this._RecreateTgtTable;
            }
            set
            {
                this._RecreateTgtTable = value;
            }
        }
		#endregion

		#region ctor 
		/// <summary>
		/// 
		/// </summary>
		public ExcelReaderInstruction():base(Guid.Empty,Guid.Empty)
		{
			this._ExcelFilePath = string.Empty;
			this._OutputFilePath = string.Empty;
            this._ColumnSizes=new Dictionary<string, Dictionary<string, int>>();
            this._ColumnDataTypes=new Dictionary<string, Dictionary<string, SimpleDataType>>();
		    this._TgtConnStr = string.Empty;
		    this._TgtSchemaName = string.Empty;
		    this._TgtTableName = string.Empty;
		    this._ClearExistingData = true;
		    this._RecreateTgtTable = true;
		}

		/// <summary>
		/// constructor, all derived class must call default constructor to initialize
		/// default settings.
		/// </summary>
		public ExcelReaderInstruction(Guid componentID, Guid jobTicketID, 
			string excelFilePath, 
			string outputFilePath,
            Dictionary<string,Dictionary<string,SimpleDataType>> colDbTypes,
            Dictionary<string,Dictionary<string,int>> colSizes,
            string tgtConnStr, string tgtSchema, string tgtTbl,
            bool clearExistingData, bool recreateTgtTable)
			: base(componentID, jobTicketID)
		{
			this._ExcelFilePath = excelFilePath;
			this._OutputFilePath = outputFilePath;
		    this._ColumnDataTypes = colDbTypes;
		    this._ColumnSizes = colSizes;
		    this._TgtConnStr = tgtConnStr;
		    this._TgtSchemaName = tgtSchema;
		    this._TgtTableName = tgtTbl;
		    this._ClearExistingData = clearExistingData;
		    this._RecreateTgtTable = recreateTgtTable;
		}
		#endregion

		#region Overrides of InstructionBase

		/// <summary>
		/// serialize settings to xml
		/// the function below is mainly for storing into database as serialized form instead of string
		/// </summary>
		public override XmlNode Serialize(ref XmlDocument xDoc, ref XmlNode parentNode)
		{
			XmlNode xNode = XmlDataUtil.AddElement(ref xDoc, parentNode, "Instruction");
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "Type", this.GetType().FullName);
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "ComponentID", this.OwnerComponentID.ToString());
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "WorkerID", this.OwnerWorkerInstanceID.ToString());
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "SubmitTime", this.SubmitTime.ToString());
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "StartTime", this.StartTime.ToString());

			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "ExcelFilePath",this.ExcelFilePath);
			XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "OutputFilePath",this.OutputFilePath);
		    XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "TgtConnStr", this.TgtConnStr);
		    XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "TgtSchema", this.TgtSchemaName);
		    XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "TgtTable", this.TgtTableName);
		    XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "ClearExistingData", this.ClearExistingData.ToString());
		    XmlDataUtil.UpdateAttribute(ref xDoc, xNode, "RecreateTable", this.RecreateTgtTable.ToString());

		    XmlNode sheetNodes = XmlDataUtil.AddElement(ref xDoc, xNode, "Worksheets");
            if(this.ColumnDataTypes !=null && this.ColumnDataTypes.Count>0)
            {
                foreach(string sheetName in this.ColumnDataTypes.Keys)
                {
                    XmlNode sheetNode = XmlDataUtil.AddElement(ref xDoc, sheetNodes, "Worksheet");
                    XmlDataUtil.UpdateAttribute(ref xDoc, sheetNode, "SheetName",sheetName);
                    foreach(string colName in this.ColumnDataTypes[sheetName].Keys)
                    {
                        XmlNode colNode = XmlDataUtil.AddElement(ref xDoc, sheetNode, "Column");
                        XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "ColumnName", colName);
                        XmlDataUtil.UpdateAttribute(ref xDoc, colNode, "DataType",this.ColumnDataTypes[sheetName][colName].ToString());
                        XmlDataUtil.UpdateAttribute(ref xDoc, colNode,"Size",this.ColumnSizes[sheetName][colName].ToString());
                    }
                }
            }

			return xNode;
		}

		/// <summary>
		/// instantiated from xml
		/// "&lt;Instructions&gt;&lt;Instruction&gt;&lt;/Instruction&gt;&lt;/Instruction&gt;", must
		/// instantiate from first child of root node
		/// </summary>
		public override InstructionBase Instantiate(XmlNode xNode)
		{
			if (xNode.Attributes.GetNamedItem("Type").Value == this.GetType().FullName)
			{
				ExcelReaderInstruction instruction = new ExcelReaderInstruction();
				instruction.OwnerComponentID = new Guid(XmlDataUtil.GetAttributeValue(xNode, "ComponentID", Guid.NewGuid().ToString()));
				instruction.OwnerWorkerInstanceID = new Guid(XmlDataUtil.GetAttributeValue(xNode, "WorkerID", Guid.NewGuid().ToString()));
				instruction.SubmitTime = DateTime.Parse(XmlDataUtil.GetAttributeValue(xNode, "SubmitTime", TimeUtil.DbMinTime.ToString()));
				instruction.StartTime = DateTime.Parse(XmlDataUtil.GetAttributeValue(xNode, "StartTime", TimeUtil.DbMinTime.ToString()));

				instruction.ExcelFilePath = XmlDataUtil.GetAttributeValue(xNode, "ExcelFilePath", "");
				instruction.OutputFilePath = XmlDataUtil.GetAttributeValue(xNode, "OutputFilePath", "");
			    instruction.TgtConnStr = XmlDataUtil.GetAttributeValue(xNode, "TgtConnStr", "");
			    instruction.TgtSchemaName = XmlDataUtil.GetAttributeValue(xNode, "TgtSchema", "");
			    instruction.TgtTableName = XmlDataUtil.GetAttributeValue(xNode, "TgtTable", "");
			    instruction.ClearExistingData =
			        bool.Parse(XmlDataUtil.GetAttributeValue(xNode, "ClearExistingData", true.ToString()));
			    instruction.RecreateTgtTable =
                    bool.Parse(XmlDataUtil.GetAttributeValue(xNode, "RecreateTable", true.ToString()));
                instruction.ColumnDataTypes=new Dictionary<string, Dictionary<string, SimpleDataType>>();
                instruction.ColumnSizes=new Dictionary<string, Dictionary<string, int>>();
                XmlNodeList sheetNodes = xNode.SelectNodes("Worksheets/Worksheet");
                if(sheetNodes !=null && sheetNodes.Count>0)
                {
                    foreach(XmlNode sheetNode in sheetNodes)
                    {
                        string sheetName = XmlDataUtil.GetAttributeValue(sheetNode, "SheetName", "");
                        Dictionary<string,SimpleDataType> colDbTypes=new Dictionary<string, SimpleDataType>();
                        Dictionary<string,int> colSizes=new Dictionary<string, int>();
                        foreach(XmlNode colNode in sheetNode.ChildNodes)
                        {
                            string colName = XmlDataUtil.GetAttributeValue(colNode, "ColumnName", "");
                            SimpleDataType dbType = (SimpleDataType) Enum.Parse(typeof (SimpleDataType),
                                                                                XmlDataUtil.GetAttributeValue(colNode,
                                                                                                              "DataType",
                                                                                                              SimpleDataType
                                                                                                                  .
                                                                                                                  TextType
                                                                                                                  .
                                                                                                                  ToString
                                                                                                                  ()));
                            int size = int.Parse(XmlDataUtil.GetAttributeValue(colNode, "Size", "4"));
                            colDbTypes.Add(colName, dbType);
                            colSizes.Add(colName, size);
                        }
                        instruction.ColumnDataTypes.Add(sheetName,colDbTypes);
                        instruction.ColumnSizes.Add(sheetName, colSizes);
                    }
                }

				return instruction;
			}
			else
			{
				throw new Exception("Unable to instantiate component: invalid type specified in xml node");
			}
		}

		#endregion
	}
}
